package org.microblog.dao.impl;

import org.apache.ibatis.annotations.Param;
import org.microblog.dao.BaseDao;
import org.microblog.dao.CollectionDao;
import org.microblog.entity.Blog;
import org.microblog.entity.Collection;
import org.microblog.util.DatabaseUtil;
import org.microblog.util.DateUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CollectionDaoImpl  extends BaseDao implements CollectionDao {
    public CollectionDaoImpl(Connection conn){
        super(conn);
    }
    @Override
    public int getCollectionCountByBid(int bid) throws SQLException{
        ResultSet rs = null;
        //获取微博收藏数
        String sql = " select count(*) from collection where bid = ?" ;
        int count = 0;

        try{
            rs = this.executeQuery(sql, bid);
            while (rs.next()) {
                count = rs.getInt("count(*)");
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return count;
    }

    @Override
    public int addCollection(Collection collection) throws SQLException{
        String now = DateUtil.now();
        String sql = "insert into collection(uid,bid,time) values(?,?,?)";
        int result = 0;
        try {
            result = this.executeUpdate(sql, collection.getUid(),collection.getBid(),now);
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return result;
    }

    @Override
    public Collection findCollection(@Param("uid") int uid,
                                     @Param("bid") int bid) throws SQLException{
        ResultSet rs = null;
        //获取微博收藏数
        String sql = " select * from collection where uid = ? and bid = ? " ;
        Collection collection = null;
        try{
            rs = this.executeQuery(sql,uid, bid);
            while (rs.next()) {
                collection = new Collection();
                collection.setId(rs.getInt("id"));
                collection.setUid(rs.getInt("uid"));
                collection.setBid(rs.getInt("bid"));
                collection.setTime(rs.getTimestamp("time")) ;
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return collection;
    }

    @Override
    public int deleteCollection(Collection collection) throws SQLException{
        String sql = "delete from collection where uid =? and bid = ?";
        int result=0;
        try{
            result = this.executeUpdate(sql, collection.getUid(), collection.getBid());
        }catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return result;
    }

    @Override
    public int deleteCollectionByBid(int bid) throws SQLException {
        String sql = "delete from collection where bid = ?";
        int result = 0;
        try{
            result = executeUpdate(sql,bid);
        }catch(Exception e){
            e.printStackTrace();
            throw e;
        }
        return result;
    }

    @Override
    public int getCollectionCountByUidAndKeyword(int uid, String keyword) throws SQLException {
        ResultSet rs = null;
        //获取收藏的微博数
        String sql = "select count(*) " +
                "from (select b1.id, b1.uid, b1.content, b1.fromBid " +
                "from collection c left join blog b1 on c.bid = b1.id and c.uid = ?) as r " +
                "left join blog b2 on r.fromBid = b2.id " +
                "where r.content like ? or b2.content like ? ";
        int count = 0;

        try{
            rs = this.executeQuery(sql, uid, "%"+keyword+"%", "%"+keyword+"%");
            while (rs.next()) {
                count = rs.getInt("count(*)");
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return count;
    }

    @Override
    public List<Blog> getCollectionsByPage(int uid, String keyword, int pageNo, int pageSize) throws SQLException {
        ResultSet rs = null;
        String fuzzyQuery = ""; //定义模糊查询部分字符串
        if(keyword!=null && !keyword.equals("")){
            fuzzyQuery = "where b1.content like '%" + keyword + "%' or b2.content like '%" + keyword + "%'";
        }
        String sql = "select b1.id, b1.uid, b1.content, b1.images, b1.time,  b1.state, u1.nickname as u_nickname, u1.image as u_image, b2.content, b2.images, " +
                "u2.nickname as f_nickname, u2.image as f_image from  " +
                "(select * from blog where id in (select bid from collection where uid = ?)) b1 left join blog b2 on b1.fromBid = b2.id " +
                "left join user u1 on b1.uid = u1.id left join user u2 on b2.uid = u2.id " +
                fuzzyQuery +
                "order by b1.time desc LIMIT ?, ?";

        List<Blog> list = null;
        try{
            rs = this.executeQuery(sql,uid, (pageNo - 1) * pageSize,pageSize);
            list = new ArrayList<Blog>();
            while (rs.next()) {
                Blog blog = new Blog();
                blog.setId(rs.getInt("id"));
                blog.setuId(rs.getInt("uid"));
                blog.setContent(rs.getString("content"));
                blog.setImages(rs.getString("images"));
                blog.setTime(rs.getTimestamp("time"));

                blog.getUser().setNickname(rs.getString("u_nickname"));
                blog.getUser().setImage(rs.getString("u_image"));
                blog.setState(rs.getInt("state"));
                if(blog.getState()==2){
                    Blog fromBlog = new Blog();
                    fromBlog.getUser().setNickname(rs.getString("f_nickname"));
                    fromBlog.getUser().setImage(rs.getString("f_images"));
                    fromBlog.setContent(rs.getString("content"));
                    blog.setOrigin(fromBlog);
                }
                list.add(blog);
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return list;
    }
}
